A First Portfolio

import pandas as pd
import pymssql
from sqlalchemy import create_engine

server = 'fs.rice.edu'
database = 'stocks'
username = 'stocks'
password = '6LAZH1'

string = "mssql+pymssql://" + username + ":" + password + "@" + server + "/" + database 
conn = create_engine(string).connect()

SELECT column_name(s)
FROM table_name
JOIN table_name ON column_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

quarterly = pd.read_sql(
    """
    select datekey, reportperiod, ticker, netinc, equity
    from sf1
    where dimension='ARQ' and equity>0
    order by ticker, datekey
    """,
    conn
)
quarterly = quarterly.dropna()

quarterly["equitylag"] = quarterly.groupby("ticker").equity.shift()
quarterly["roeq"] = quarterly.netinc / quarterly.equitylag

# save last report for each firm
quarterly = quarterly.groupby("ticker").last()
quarterly = quarterly[quarterly.reportperiod.astype(str)>="2022-06-01"]

# drop variables other than roeq and ticker (ticker=index)
quarterly = quarterly[["roeq"]]

daily1 = pd.read_sql(
    """ 
    select date, ticker, pb, marketcap
    from daily 
    where date>='2023-01-01'
    order by ticker, date
    """,
    conn
)
daily1 = daily1.dropna()
daily1 = daily1.groupby("ticker").last()

daily2 = pd.read_sql(
    """ 
    select date, ticker, close_ as price
    from sep 
    where date>='2023-01-01'
    order by ticker, date
    """,
    conn
)
daily2 = daily2.dropna()
daily2 = daily2.groupby("ticker").last()

df = pd.concat((quarterly, daily1, daily2), axis=1)
df.info()

df = df[df.price>5]
df.shape

df["size_rank"] = df.marketcap.rank(ascending=False)
df = df[df.size_rank > 500]

filter1 = df.roe >= df.roe.quantile(0.7)
filter2 = df.pb <= df.pb.quantile(0.3)
df = df[filter1 & filter2].copy()
ticks = df.index.to_list()
len(ticks)

positions = trading_client.get_all_positions()
sers = [pd.Series(dict(x)) for x in positions]
df = pd.concat(sers, axis=1)
df.T

params = StockLatestQuoteRequest(symbol_or_symbols=ticks)
quotes = data_client.get_stock_latest_quote(params)

# convert to dataframe
sers = [pd.Series(dict(x)) for x in quotes.values()]
df = pd.concat(sers, axis=1)
df.columns = [x for x in quotes]
df = df.T
df = df.drop(columns=["symbol"])
df.info()

df["trade"] = df.ask_price.apply(lambda p: int(1000/p) if p !=0 else 0)
ticks = df[df.trade>0].index.to_list()

for tick in ticks: 
    market_order_data = MarketOrderRequest(
        symbol=tick,
        qty=df.trade.loc[tick],
        side=OrderSide.BUY,
        time_in_force=TimeInForce.DAY
    )
    market_order = trading_client.submit_order(
        order_data=market_order_data
    )